Param(
[string]$server='FIBICH-ACER\SQLEXPRESS',
[string]$db='CensusData',
[string]$userName="",
[string]$password=""
)
#----------------------------
# execute-scriptUnValidated()			
#----------------------------
function execute-scriptUnValidated {
	#Trusted Connection
	if ($userName -eq ""){
		Invoke-SQLcmd -Query 'Select * from dbo.database_version_information;' -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600;
	}
	else {
		Invoke-SQLcmd -Query 'Select * from dbo.database_version_information;' -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password;
	}
};

#------------------------
# execute-script()			
#------------------------
function execute-script ($scriptName) {
	$queryString="Select 1 from dbo.database_version_information where script_name=replace(`'$scriptName`','Undo_','') and undone=1;";

	if ($userName -eq "") {
	if ((Invoke-SQLcmd -Query $QueryString -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 |measure-object).count -eq 0) {
		write-host -ForegroundColor Cyan "Executing script $ScriptName(DB:$db)" -NoNewLine;
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600
		write-host -ForegroundColor Cyan "....Complete";
	}
	else {
		write-host -ForegroundColor Cyan "$ScriptName - Script has already been executed, it will be skipped ($db)";
	}
}else {
	if ((Invoke-SQLcmd -Query $QueryString -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password |measure-object).count -eq 0) {
		write-host -ForegroundColor Cyan "Executing script $ScriptName(DB:$db)" -NoNewLine;
		Invoke-SQLcmd -InputFile $scriptName -Database "$db" -ServerInstance "$server" -QueryTimeOut 3600 -Username $userName -Password $password;
		write-host -ForegroundColor Cyan "....Complete";
	}
	else {
		write-host -ForegroundColor Cyan "$ScriptName - Script has already been executed, it will be skipped ($db)";
	}
}
};


#------------------------
# Error Action			
#------------------------
$ErrorActionPreference="STOP";

#------------------------
# Main()			
#------------------------
$ScriptPath=split-path $myInvocation.InvocationName
start-transcript -path $scriptPath\Execute_Undo_US_Census_Data.log -force;

write-host 'Warning this script will undo the Execute_US_Census_Data.ps1' -foregroundcolor yellow;
write-host 'Are you sure you would like to continue?(Y/N) ' -foregroundcolor yellow -NoNewLine;
$a=read-host;
if ($a -eq 'Y') {
	write-host 'UNDO!' -foregroundcolor yellow;

	#-------
	#Just in case SQLPS Module invoke-sqlcmd leaves us out in its psdrive
	#-------
	$location=get-location;


	execute-script 'Undo_CensusData_V0R00009.sql';
	execute-script 'Undo_CensusData_V0R00008.sql';
	execute-script 'Undo_CensusData_V0R00007.sql';
	execute-script 'Undo_CensusData_V0R00006.sql';
	execute-script 'Undo_CensusData_V0R00005.sql';
	execute-script 'Undo_CensusData_V0R00004.sql';
	execute-script 'Undo_CensusData_V0R00003.sql';
	execute-script 'Undo_CensusData_V0R00002.sql';
	execute-script 'Undo_CensusData_V0R00001.sql';
	
	#-------
	#Just in case SQLPS Module invoke-sqlcmd leaves us out in its psdrive
	#-------
	set-location $location; 
	
}else{
	write-host 'OK QUIT!';
	return;
}
stop-transcript